If you have several tests to combine, it may be helpful to write it on paper first, to get an idea of where to include parenthesis.
Note: When using parentheses, it is possible to generate the error message Too Many Windows. To avoid this, the application size may be increased from the Finder. Choose Get Info with the Atlantis™™ Data Publisher selected, and then change the Application Memory Size.
* Chapter *
* Section *
11.1 General
This chapter tells you how to edit a design document, including how to:
- move a field
- cut and paste
- insert and delete lines
- center a line
- use the show fields window
- edit a field selection
The editing procedures described in this chapter are all done within a design window, not on the printed report. If you need to make changes on a report which has been printed, then change the layout (through the design window) and re-run the report.
To start, select Open... from the File menu and select the layout you wish to edit.
* Section *
11.2 Moving a Field
You can move a field entry by clicking the field to select it and then dragging it to the new location. You can quickly move a field entry or a text entry this way. Check the line types on the left border when moving fields.
* Section *
11.3 Cut and Paste
This selection allows you to move words and/or sections of text or numbers in the design window. In order to do a cut and paste on the screen, you must always do the cut first. Move the pointer to the section of the screen that you have to cut. Select the section to cut, to highlight it, then select Cut from the Edit menu. Then click at the new location and select Paste from the Edit menu.
If you are moving field selections, note the line types so that you are using header, detail and subtotal lines correctly. (See Chapter Five, Section 5.5 for details on line types.)
You can use this feature to move anything in the design window, including field sections and text.
If you have moved something and want to clear it out, click to highlight that section, and then select Clear from the Edit menu.
* Section *
11.4 Adding or Deleting Line Types
You can change the report format to add or delete any of the line types described previously.
Deleting a line type is done by using the <BACKSPACE> key. Position the insertion point next to the line type and then press the <BACKSPACE> key to delete lines, one at a time.
Adding a line is done by using the <RETURN> key. Position the insertion point next to the line type (eg. D) and then press the <RETURN> key to add lines, one at a time.
Adding a line type is done by moving the insertion point to the end of the line just before the missing line type to be opened, and pressing the <RETURN> key. Use the arrow keys to make sure there are no spaces to the right of the insertion point. Or, better yet: use the arrow keys to ensure that the insertion point is at the right edge of the design window.
Note the following additional notes on adding or deleting line types:
- It is possible to delete all the lines of a line type, but you must always have at least one Detail line, or no information will appear on the report.
- Position the insertion point all the way to the left of the line, or spaces will be inserted on the line between the left margin and the insertion point.
* Section *
11.5 Centering Lines and Titles
Generally, the default centering position for the automatic banner and report titles is the center of the ruler. If the ruler is changed, the center position is changed, as well.
A ^ symbol to the left of the ruler is used to indicate the centering position. If the ^ is on the left side of the ruler, the centering position is the mid-way mark on the report width. On a report 8 1/2 inches wide, the center would be at 4 1/4 inches. If you would like to keep the report width the same, yet change the centering position for titles, drag the ^ symbol to the point on the ruler that you want to use as the new centering position. The centering position will then move over to that position.
* Section *
11.6 Show Fields
If you would like to view your report, including any changes made, select Show Fields... from the Data menu. The following information will be displayed in the window:
- line type and number
- column number
- what to print at that location (eg. field, literal text value, field extensions)
This allows you to quickly locate any field that needs to be changed.
* Section *
11.7 Editing a Field
There are two ways to edit a field: one involves changing the field specification itself, including any field extensions. The second involves determining the printing characteristics.
If you need to change the output format for a field, refer to Chapter 14 of this manual. This chapter discusses editing the field specification only, not the output format.
To start, highlight the field that you want to change, then select Edit a Field... from the Edit menu.
A window will appear with all the selections you made for that field (such as field name, field modifiers, etc.)
The field will be highlighted in the dialog box. If you press the <DELETE> key, the entire field will be deleted and you can start over again.
If you don't want to delete the field, you can click again to turn off the highlight and proceed to edit the selection.
To insert within the field, position the pointer at the start of the insert location and then include your insert. The rest of the field will move down as needed.
You can add field names, field extensions, change parentheses and make any other revisions to the field, as long as you use exact field names and extensions. The report writer will not alert you to incorrect spelling of fields names; however, it will alert you to errors in spacing.
If you change parentheses, be sure that you have a matching right parentheses for each left parentheses.
When you are finished editing, click OK. The edit window will disappear as it processes the changes. Click once more to cancel the highlighting in the window. If you change your mind about doing an edit, just select Cancel while in the edit mode and the field will not change.
To view the changes made during the edit, select Show Fields... from the Data menu. See Chapter 15 for details on editing the format of a field (as output).
* Chapter *
* Section *
12.1 Conditional Printing
This feature of the ™™ Data Publisher allows you to choose when to print a field of a record. The field will be printed only if a certain condition is met. (Other fields of the same record may be included in the report, however.) A test is done by the program on that field to decide whether or not to include the field in the report and in the calculations, if any.
Note: If you would like to test a record (not just a field), see Chapter Ten, Choosing When to Include Records.
To enter one of these tests on a field, after you have selected Choose Fields from the Design menu, and then selected the field, select:
- <Modify Field Further>
- <Conditional Fields>
- the field name on which to base the test
- select the description of comparison symbol
- select another field name or enter a literal text value
When selecting the field on which to base the test, this can be a field which you are including in the report, or you can base the test on another field entirely.
You can compare that field with another field from the file or with a literal text value. For example, you may want to include the customer phone number if the sale amount is greater than $1,000. Your selections may may go like this:
- <Choose Fields>
- Phone number
- <Modify Field Further> - <Conditional fields (.if)>
- <Sale amount>
- <Is greater than>
- <Enter literal text value>
- 1000
- <OK>
to get: phone number.if(sale amt > "1000")
The program automatically inserts the parentheses when selecting an .if test. It also includes quotation marks around the literal text value, so do not type this in.
* Section *
12.2 Double If Tests
You can combine two or more if tests into a single selection. Select & (and) or : (or) to combine the tests.
Example: phone no.if(slm = "BB" : slm = "HE")
The program will insert parentheses around the entire .if test when you use the and or selections. If you would like to review the test at any time, select Show Fields from the Design menu.
Example: sale amt.if(slm = "BB"&sale date =
"01/01/91")
In the example above, the only sale amounts which would be included in the report are those where both the salesman is BB and the sale date is 01/01/91.
You can use as many parentheses as are needed to enclose the if tests (and any calculations). Just select use parenthesis to open and when done with entry or close parenthesis to get the close parenthesis. If you later need to insert or delete parentheses, you can do so manually by selecting Show Fields from the Design Menu, and then editing the field.
* Section *
12.3 If Tests Combined with Calculations
You can use multiple if tests along with calculations. Use the parentheses to indicate the order of evaluation.
If the program is required to perform more than one calculation to get the answer you want printed on your report, then you must tell it what to do first.
For example, you may need to subtotal an amount field on your report, and then multiply that subtotal by .1 to get a commission rate, but only want to include records for a certain salesman. The salesman selection would come first, since other records should not be included in the totals.
Use parentheses to indicate the order of evaluation.
When combining if tests, you need to plan ahead of time when and where to insert the parentheses, and then select them from the windows accordingly. A single if test will insert the parentheses automatically, but on multiple if tests and combining if tests and calculations, you need to plan where to include parentheses and select the special command of use parentheses to open each level and close parenthesis to close it.
* Section *
12.3.1 Evaluation Order
The report writer program uses the following order in calculations, from left to right:
1. ( ) Parentheses
2. -> => +> *> . Look-ups and "Dot" commands
3. * / Multiplication and division
4. + - Addition and subtraction
5. = < <= > >= Comparisons
6. & : And or
7. , ; Field concatenations
and from left to right across the line
Note that "dot" commands follow after parentheses, but before other math calculations. "Dot" commands include if tests, but also include the mathematical extensions for subtotals and others. If there is more than one dot command on a field specification, the program uses the first one (to the left) first.
If the simple arithmetic is enclosed in parentheses, then the calculations within the parentheses will be performed in order of multiplication, division, addition and subtraction, and the evaluation order of these can be further changed by use of more parentheses.
Following are two examples of if tests combined with calculations:
(Since this was entered on a subtotal line, Atlantis™ looks to the last detail line (of each level break) to get the customer balancing method for the if test).
- selects records where the customer balancing method is "B")
- totals those records
Note that there are two dot commands on this report. The if test was done first because that is the first dot command, when read from left to right by the program.
It is helpful to watch the field in the field window, as it is created. Also, you can do sample runs of your report, selecting only a few records when running the report.
* Section *
12.4 Editing If Tests
You can view your specification to check your evaluation order and parentheses. Select Show Fields from the Design menu to view the test.
You can change the 'If' test by editing the field. Highlight the field and then use the Edit menu. Insert or delete parentheses and text as needed. Then press the <Enter> key.
Note: When using parentheses, it is possible to generate the error message Too Many Windows. To avoid this, the application size may be increased from the Finder. Choose Get Info with the Atlantis™ Data Publisher selected, and then change the Application Memory Size.
* Chapter *
* Section *
13.1 Mathematical Calculations
There are several field extensions which can be used to perform mathematical calculations. These are available by selecting Choose Fields from the Data menu, and then:
- the field name
- <Modify Field Further>
- <Calculated Fields>
- Running Balance total
- Percent of total
- Deviation from average
- Total (sub or grand)
- Average amount
- Count of records
- Record number
- Maximum or minimum value
Some of these calculations can be run on detail lines (such as running balances and record numbers). Other calculations work best on total lines (line types 1-9).
* Section *
13.2 Calculations with Grand Totals Only
To perform any of the mathematical calculations listed above, on a report with no subtotals and grand totals only, first position your insertion point on a 1 line type. Then select Choose Fields from the Data menu, and
- the field name
- <Modify Field Further>
- <Calculated Fields>
- the calculation
- the subtotal level (use the first one)- <OK>
When your report is written, the calculations will be at the bottom of the report.
If you would like to get subtotals on your report, you must first set total breaks on detail lines. Then the program knows how to group the data. See Section 12.3.
* Section *
13.3 Setting Subtotal Levels
If you would like to use subtotals on your report, set the fields on which the subtotals are to be based as level breaks when entering them on the detail lines. A level break defines the beginning of a group and the subtotal line is the end of the group.
These fields need to be listed as sort fields, in the same order that they are set as total breaks.
For example, you could have a report designed to give you a breakdown of which products are going to which customers, and when. The design could look something like this:
Product Code Customer Name Sale Date Qty Amt Paid
XXXXXXXXX XXXXXXXXXXXXX 99/99/99 99 999.99
In the example, you could have total breaks at:
- product code
- customer name
This provides you with a total of the Amt Paid field for each different product code and additional subtotals showing the dollar value of each customer's purchases of each product. (This can be broken down any way that you need.)
You can include up to nine total breaks in one report. You can do any number of calculations that you need at each subtotal level, on any numeric field in the report.
Note that level breaks and subtotal levels are inversely indicated. In other words, later level breaks (to the right) have the lowest level subtotals associated with them.
- In the example above, product code would be the first field laid out on your design on the detail line. (It would also be the first sort field.) When you choose that field, make an additional selection of set as total break to provide subtotals for that field. This will be the highest subtotal level and will give you the largest subtotals. (Although, if no other subtotal levels are set, the subtotal level would be 1).
- In the example above, customer name would also be set as a total break, and would give you the next highest subtotals for the report.
- In the example, since customer name is the last total break set, you need to select based on 1st subtotal level when asking for mathematical calculations for the customer name group.
The first subtotal level is the smallest subtotal (and is the last subtotal level set). Calculations based on the first subtotal level are entered on a type of line 1.
- Then to add up the amount field for each product code, select based on 2nd subtotal level and enter on a 2 type of line. (You will have larger subtotals).
- In the above example, if you want a grand total for the report, use type of line 3 and select based on 3rd subtotal level when doing the mathematical calculation.
Note: If you will not need subtotals for your report, but grand totals only, then you do not need to set a total break. Just use based on first subtotal level.
* Section *
13.4 Selecting Mathematical Calculations
To include mathmetical calculations in your report, as in the example above, first determine on which subtotal level you want to base the calculation and move the pointer to that line type.
When the insertion point is located on the correct line type (1-3), select:
- <Design>
- <Choose fields>
- select a field name
- <Modify Field Further>
- select a calculation
- the subtotal level (same as line type used)
- <OK>
When selecting the subtotal level, note that it must match the line type. Also note that as you go further down the design (higher line types) the subtotals will get larger (and they are based on larger sort groups).
The grand total should be at the bottom of the report.
If you have not set up any total breaks for your report, select based on 1st subtotal level and you will get grand totals.
* Section *
13.4.1 Multiple Calculations - Evaluation Order
If the program is required to perform more than one calculation to get the answer you want printed on your report, then you must specify the order in which calculations should be done.
For example, you may need to subtotal an amount field on your report, and then multiply that subtotal by .1 to obtain a commission rate.
Use parentheses to indicate the order of evaluation. As an example, select:
- <Choose fields>
- <Special commands>
- <Use parentheses>
- Amt recd (or other field name)
- <Modify field further>
- <Calculated fields>
- <Total (sub or grand)>
- <Based on subtotal level 1>
- <Close parenthesis>
- <Multiply by Field>
- <Special commands>
- <Enter literal text value>
- .1
- <OK>
to get: (amt recd.total[1])*".10"
The program will perform the calculation first on the subtotal (within parentheses) and then the 10%.
If you would like to manually add or change the evaluation order, without making selections through the windows, select Edit a Field from the Edit Menu. Then insert or move the parentheses as needed.
The program uses the following order in calculations, from left to right:
1. ( ) Parentheses
2. -> => +> *> . Look-ups and "Dot" commands
3. * / Multiplication and division
4. + - Addition and subtraction
5. = < <= > >= Comparisons
6. & : And or
7. , ; Field concatenations
and from left to right across the line
Therefore, regarding mathematical calculations, the program will calculate in the following order:
- calculations enclosed in parentheses - calculations including "dot" commands, including .total and other extensions, and also including .if commands
- simple arithmetic calculations (not enclosed in parentheses)
and from left to right across the line.
If the simple arithmetic is enclosed in parentheses, then the calculations within the parentheses will be performed in order of multiplication, division, addition and subtraction, and the evaluation order of these can be further changed by use of more parentheses, in which case, the innermost set of parentheses is done first.
Following are two examples of calculations in which the use of parentheses produces two different results:
(Since this was entered on a subtotal line, the report writer looks to the last detail line (of each level break) to get the customer balancing method for the if test).
- selects records where the customer balancing method is "B")
- totals those records
Note that there are two dot commands on this report. The if test was done first because that is the first dot command, when read from left to right by the program.
It is helpful to watch the field specification as it is being created so that you can see where the parentheses are placed and the evaluation order of multiple calculations.
It is sometimes helpful to run the report, selecting only a few records (on the output screen) to check your mathematical entries. Then, if necessary, you can go back into the design and select Edit a Field from the Edit menu.
Note: When using parentheses, it is possible to generate the error message Too Many Windows. To avoid this, the application size may be increased from the Finder. Choose Get Info with the Atlantis™ Data Publisher selected, and then change the Application Memory Size.
* Chapter *
* Section *
14.1 Date Commands
There are several date commands available. These are used in combination with date fields selected through the windows and with literal values. You can also make calculations using time.
When you want to perform calculations on dates, you must first translate the date into a day number. This is called a Julian day. After the calculation is performed on the Julian day, the day can be translated back to a date.
These translations are done through the use of Date Commands. Date commands are selected by first selecting a date field and then
- Modify Field Further
- Date commands
Date commands then available include:
- Current Age of Date
- Julian Days of Date (1900)
- Date of Julian Days (1900)
- Julian Days of date (yr 0)
- Date of Julian Days (yr 0)
- Seconds to HH:MM:SS
* Section *
14.2 Current Age of Date
Use this selection to find the age of the date in your data file. The date in your data file is compared to the system date of your computer.
The field extension entered by the program here is .age.
The calculation is:
(System date) minus (date from data file)
Both dates are translated to day numbers, so that the calculation can be done. The number that prints on your report, is the number of days that have passed since the data file date. This is useful for creating aging reports.
Window selections are made as follows:
- date field (from your data file)
- <Modify Field Further>
- <Date Commands>
- <Current Age of Date>
* Section *
14.3 Julian Days of Date (1900) - .days
This field extension is used to translate a date into a Julian day number from the year 1900 forward.
Example: invoice date.days
The above example would replace the invoice date with the appropriate Julian day number (day numbers start with January 1, 1900).
* Section *
14.4 Date of Julian Days (1900) - .date
This field extension is used to translate a Julian day number (January 1, 1900 base) into a date.
Example: (invoice date.days + "30").date
The above example would first perform the translation to a Julian day number, add 30 (days), and then change the new Julian day number back into a date. Note that literal text values can also be used with date calculations. In the above example 30 is the literal text value.
* Section *
14.5 Julian Days of Date (Yr 0) - .days[2]
This field extension is the same as .days, except that the day number will start with January 1, 0000.
Example: invoice date.days[2]
The example above would replace the invoice date with the appropriate Julian day number (day numbers start with January 1, 0000).
* Section *
14.6 Date of Julian Days (Year 0) - .date[2]
This field extension works the same as .date, except that the translation is based on the January 1, 0000.
Example: (invoice date.days[2] + "30").date[2]
The example above would first perform the translation to a Julian day number (based on January 1, 0000), add 30 (days), and then change the new Julian day number back into a date.
* Section *
14.7 Seconds to HH:MM:SS
The field extension is used to translate seconds into hours, minutes and seconds. This is useful if you want to do a calculation based on time.
You can use this along with the timekeeping device in your computer and times entered in your data files, if needed.
* Section *
14.8 Performing Calculations with Julian Dates
These field extensions can be entered manually or selected through the windows. Following is a sample window selection which adds 31 to the date in your data file:
- <Special Commands>
- <Special Field Commands>
- <Use Parentheses>
- INVOICE DATE
- <Modify Field Further>
- <Date commands>
- <Julian days of date> (to get day number)
This is same as .days extension
- <Add to Field>
- <Special Commands>
- <Special Field Commands>
- <Enter literal text value>
- 31 <Enter>
- <Modify Field Further>
- <Date Commands>
- <Date of Julian Day>
- OK
Example: (invoice date.days+"31").date
If your dates are already stored in Julian Day format, then you would not need to use the .days extension to get a Julian Day number. However, you may want to translate your Julian Day into a date for output.
* Chapter *
* Section *
15.1 Changing Field Formats
There are several ways that you can change the format of your fields so that your numbers, dates or characters are printed in a different format. When your fields appear in your design window, they will appear with the default formats, which are:
Numeric fields - 999,990.00-
Date fields - mm/dd/yy
Alpha-numeric fields - X's
Changing a field format is done by typing over the entries in your design window. Following is a list of the format options available:
- Removing commas on numeric fields
- Removing leading zeros
- Adding decimal Places
- Rounding numbers and decimals
- Inserting dollar signs
- Plus and minus signs
- Embedded plus or minus signs
- Embedded parentheses
- Asterisks
- Enclosing negative numbers in parentheses
- Changing the size of numeric fields
- Changing characters to lower case
- Changing date formats
- Justifying and centering fields
In order to change a format, you must first make your field selections. Then after the selection is displayed in the design window, type in the format changes right in the design window. The changes on numeric fields will appear in the field (at show fields) with a .format field extension. The format extension is created when you make changes to the field in the window.
* Section *
15.2 Removing Commas
To remove a comma from the number, move the insertion point just to the right of the comma and press the delete key. The numbers will print on the report without commas.
* Section *
15.3 Leading Zeros
Use 9's and 0's to indicate whether or not to include leading 0's on the report. Leading 0's means the number will print with all zeros, even if they are at the beginning of the number.
A 9 on the design screen tells the program not to print leading zeros. There will be a blank space instead.
A 0 on the design screen tells the program to print any and all numbers, even leading zeros.
You can change the 9's and 0's. The easiest way to do this is to move the insertion point just to the right of the number and then delete the existing characters and insert the others.
Example: 000,000.00-.
In the above example, a 20 for instance would then print as 000,020.00. This could be changed back by replacing the zeros with nines.
* Section *
15.4 Adding Decimal Places
If you would like to add more digits to the right of the decimal point, move the insertion point just to the right of the last digit and insert zeros on the screen.
Example: 999,990.00000
* Section *
15.5 Rounding Numbers and Decimals
If you would like your numbers rounded up, use a 5 to indicate a digit position which will be rounded off. All digits to the right of this position are displayed as 0's (unless 0's are turned off). You can do this on either side of the decimal point. The 5 will round the number away from 0.
Example: 999.959
The above example would give you a rounded number at the second decimal place instead of the third.
* Section *
15.6 Printing Dollar Signs
If you would like dollar signs to print on your report, insert a $ just before the number.
Example: $999,990.00
The dollar sign will then print at that column position on the report.
Example output: $ 50.00
In the example above, you will have spaces between the $ and the start of the number if your numbers are set up to print without leading zeros.
Use additional dollar signs to move over the dollar sign when there are leading zeros.
Example: $$$,$$9.99
to get: $12.34 (if value is 12.34)
* Section *
15.7 Plus and Minus
The minus sign (-) on the screen on numeric fields is used to get negative numbers printed with a minus sign. Positive numbers will have no positive sign.
Example: 999,990.00-
If you would like to include the positive sign in your printed report, change the minus sign to a plus sign (+). Each number will contain either a plus or minus sign.
Example: 999,990.00+
You can enter more than one symbol in the design window to replace leading zeros with spaces and to display the sign before the number, instead of after it.
Example: ---,--9.99
on a value of 1.23 would give you 1.23 if positive or -1.23 if negative
